﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Public;

namespace DAL
{
    public class TonGiaoDAL
    {
        public static List<TonGiaoPublic> LoadTonGiao()
        {
            var tongiao = Common.db.ExecuteStoreQuery<TonGiaoPublic>(@"select * from TonGiao");
            return tongiao.ToList();

            //List<TonGiaoPublic> list = new List<TonGiaoPublic>();
            //var query = from q in Common.db.TonGiaos
            //            select q;
            //foreach (var item in query)
            //{
            //    TonGiaoPublic tg = new TonGiaoPublic(item.MaTG, item.TenTonGiao);
            //    list.Add(tg);
            //}
            //return list;
        }
        public static TonGiaoPublic GetTonGiaoByMaTG(int MaTG)
        { 
            var tongiao=Common.db.ExecuteStoreQuery<TonGiaoPublic>(@"select TenTonGiao from TonGiao where MaTG="+MaTG);
            return tongiao.ToList()[0];
        }
        public static List<TonGiaoPublic> LoadBieuDoTonGiao()
        { 
            string chuoi=@"
                        select first.MaTG, first.TenTonGiao, first.SL
                        from (
	                        select top 5 T.MaTG,TenTonGiao,COUNT(N.MaTG) as SL
	                        from TonGiao T join Nhankhau N
	                        on T.MaTG = N.MaTG
	                        group by T.MaTG, TenTonGiao
	                        order by SL desc) first
	
                        union all
	
                        select last.MaTG, last.TenTonGiao, last.SL
                        from (
	                        select cast(0 as tinyint) as MaTG, 'Khác' as TenTonGiao,ISNULL(sum(t2.SL),0) as SL
	                        FROM
	                        (   SELECT ROW_NUMBER() OVER(ORDER BY SL desc) AS row, t1.*
		                        FROM
		                        ( 	select COUNT(N.MaTG) as SL
			                        from TonGiao T left join Nhankhau N
			                        on T.MaTG = N.MaTG
			                        group by T.MaTG
		                        ) t1
	                        ) t2
	                        WHERE t2.row BETWEEN 6 AND 100) last; 
                        ";
            var tg = Common.db.ExecuteStoreQuery<TonGiaoPublic>(chuoi);
            return tg.ToList();
        }
    }
}
